package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.postgresql.util.PSQLException;

import util.Constants;
import base.MovieDetails;
import base.MovieInfo;

public class ConnectionDB{
	
	Statement statement;
	Connection conn;
	String sqlStatement;
	
	public void ConnectDB() throws SQLException, ClassNotFoundException{
		
	}
	
	// 1. Generate all movie records in database.
	public ArrayList<MovieInfo> SelectAll() throws ClassNotFoundException, SQLException{
		Class.forName("org.postgresql.Driver");
		conn = DriverManager.getConnection(Constants.JDBC_STRING, Constants.USERNAME, Constants.PASSWORD);
		statement = conn.createStatement();
		//===============================================
		ArrayList<MovieInfo> movieList = new ArrayList<MovieInfo>();
		try{
			ResultSet res = statement.executeQuery("SELECT * FROM movies");
			if (res!=null){
				while (res.next()){
					MovieInfo movie = new MovieInfo();
					movie.setMovieId(res.getInt("id"));
					movie.setTitle(res.getString("title"));
					movie.setGenre(res.getString("genre"));
					movie.setYear(res.getString("year"));
					
					//Get all information includes Detail Information from moviedetails table
					
					movie = new ConnectionDB().Select("" + movie.getMovieId());
					System.out.println("link = " + movie.getDetails().getLinkToIMDB());
					movieList.add(movie);
				}
			}
			res.close();
			statement.close();
			conn.close();
			return movieList;
		}catch(NullPointerException e){
			return movieList;
		}
	}
	
	public MovieInfo Select(String movieId) throws ClassNotFoundException, SQLException{
		Class.forName("org.postgresql.Driver");
		conn = DriverManager.getConnection(Constants.JDBC_STRING, Constants.USERNAME, Constants.PASSWORD);
		statement = conn.createStatement();
		MovieInfo movie = new MovieInfo();
		MovieDetails movieDetails = new MovieDetails();
		
		//===================GET INFORMATION FROM "movies" table============================
		
		sqlStatement = "SELECT * FROM movies where id = " + movieId;
		
		ResultSet res = statement.executeQuery(sqlStatement);
		if (res!=null){
			while (res.next()){
				movie.setMovieId(res.getInt("id"));
				movie.setTitle(res.getString("title"));
				movie.setGenre(res.getString("genre"));
				movie.setYear(res.getString("year"));
				movie.setMovieDetailsId(res.getString("moviedetailsid"));
			}
		}
		
		//===================GET INFORMATION FROM "moviedetails" table============================
		
		sqlStatement = "SELECT * FROM moviedetails where id = " + movie.getMovieDetailsId();
		
		res = statement.executeQuery(sqlStatement);
		if (res!=null){
			while (res.next()){
				movieDetails.setMoviedetailsId(res.getInt("id"));
				movieDetails.setCountry(res.getString("country"));
				movieDetails.setLanguages(res.getString("languages"));
				movieDetails.setRating(res.getString("rating"));
				movieDetails.setNumberofVotes(res.getString("numberofvotes"));
				movieDetails.setLinkToIMDB(res.getString("linktoimdb"));
			}
		}
		movie.setDetails(movieDetails);
		//====================
		statement.close();
		conn.close();
		return movie;
	}
	
	public void Insert(MovieInfo m) throws ClassNotFoundException, SQLException{
		System.out.println("connectiondb- insert");
		MovieDetails movieDetails = m.getDetails();
		String sqlGetNewId="";
		Class.forName("org.postgresql.Driver");
		conn = DriverManager.getConnection(Constants.JDBC_STRING, Constants.USERNAME, Constants.PASSWORD);
		statement = conn.createStatement();
		//=========================Insert Data in MovieDetails table====================
		try{
			ResultSet res = statement.executeQuery("SELECT MAX(id) from moviedetails");
			if (res!=null){
				while (res.next()){
					sqlGetNewId = "" + (res.getInt("MAX")+1);
				}
			}
		}catch(PSQLException e){
			e.printStackTrace();
			System.out.println("Psql");
			sqlGetNewId = "1";
		}
		sqlStatement = "INSERT INTO moviedetails VALUES(" + sqlGetNewId 
		+  ",'" + movieDetails.getCountry() + "','" + movieDetails.getLanguages() +"','" + movieDetails.getRating()  
		+  "','" + movieDetails.getNumberofVotes() + "','" + movieDetails.getLinkToIMDB() +
		"')";
		System.out.println("sql statement = " + sqlStatement);
		try{
			statement.executeQuery(sqlStatement);
		} catch (PSQLException e) {
			e.printStackTrace();
		}
		
		
		//=======================Insert data into Movies table====================
		
		m.setMovieDetailsId(sqlGetNewId); //Add Id of Moviedetails Record into MovieInfo object
		try{
			ResultSet res = statement.executeQuery("SELECT MAX(id) from movies");
			if (res!=null){
				while (res.next()){
					sqlGetNewId = "" + (res.getInt("MAX")+1);
				}
			}
		}catch(PSQLException e){
			sqlGetNewId = "1";
		}
		sqlStatement = "INSERT INTO movies VALUES(" + sqlGetNewId 
		+  ",'" + m.getTitle() + "','" + m.getGenre() +"','" + m.getYear()  
		+  "','" + m.getMovieDetailsId() + "')";
//		System.out.println("sql statement = " + sqlStatement);
		try{
			statement.executeQuery(sqlStatement);
		} catch (PSQLException e) {
//			e.printStackTrace();
		}
		statement.close();
		conn.close();
	}
	
	public MovieInfo Update(MovieInfo movieInfo) throws ClassNotFoundException, SQLException{
		System.out.println("Update");
		Class.forName("org.postgresql.Driver");
		conn = DriverManager.getConnection(Constants.JDBC_STRING, Constants.USERNAME, Constants.PASSWORD);
		statement = conn.createStatement();
		//=========================UPDATE movies DATA ==================
		sqlStatement = "UPDATE movies SET";
		sqlStatement += " genre='" + movieInfo.getGenre()+"'";
		sqlStatement += " , year=" + movieInfo.getYear();
		sqlStatement += " WHERE id=" + movieInfo.getMovieId();
		try {
			statement.executeQuery(sqlStatement);
		} catch (PSQLException e) {
			// e.printStackTrace();
		}
		//===================update MOVIEDETAILS data=================
		MovieDetails movieDetails = movieInfo.getDetails();
		sqlStatement = "UPDATE moviedetails SET ";
		sqlStatement += " country='" + movieDetails.getCountry()+"'";
		sqlStatement += " , languages='" + movieDetails.getLanguages()+"'";
		sqlStatement += " , rating='" + movieDetails.getRating()+"'";
		sqlStatement += " , numberofvotes='" + movieDetails.getNumberofVotes()+"'";
		sqlStatement += " , linktoimdb='" + movieDetails.getLinkToIMDB() +"'";
		sqlStatement += " WHERE id=" + movieInfo.getMovieId();
		try {
			statement.executeQuery(sqlStatement);
		} catch (PSQLException e) {
			// e.printStackTrace();
		}
		statement.close();
		conn.close();
		return movieInfo;
	}
	
	public void Delete(String id) throws ClassNotFoundException, SQLException{
		System.out.println("delete in progress");
		Class.forName("org.postgresql.Driver");
		conn = DriverManager.getConnection(Constants.JDBC_STRING, Constants.USERNAME, Constants.PASSWORD);
		statement = conn.createStatement();
		//delete from movies table
		sqlStatement = "DELETE FROM movies where id=" + id;
		try{
			statement.executeQuery(sqlStatement);
		}catch(PSQLException e){
			// e.printStackTrace();
		}
		//delete from moviedetails table
		sqlStatement = "DELETE FROM moviedetails WHERE id=" + id;
		try{
			statement.executeQuery(sqlStatement);
		}catch(PSQLException e){
			// e.printStackTrace();
		}
		statement.close();
		conn.close();
	}
}